package edu.colorado.karl.databases;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Class that populates the LongTerm Database with info from the WebCTRL
 * Database.  When run, any new entries found in the WebCTRL Databases are 
 * copied to the LongTerm Database
 * @author Michael Knudson
 * @version 5/4/08
 */
public class DatabaseUpdater {
	//	Used for log4j logging
	private static final Log log = LogFactory.getLog(DatabaseUpdater.class);

	/**
	 * Sole constructor.
	 */
	public DatabaseUpdater() {

	}

	/**
	 * Updates the IntelliCORE databases with new WebCTRL Trend information.
	 */
	public void update() {
		Connection webctrl = WebCTRLDatabaseConnector.getConnection();
		Connection longterm = LongTermDatabaseConnector.getConnection();
		int maxTID = 0;

		//	Get the number of sensors
		try {
			Statement s = longterm.createStatement();
			s.executeQuery("select MAX(TID_) from TRENDDATA;");
			ResultSet rs = s.getResultSet();
			maxTID = rs.getInt(1);
			
			if(maxTID == -1)
				throw new SQLException("Database Updater unable to determine number of sensors.");

			for(int i=0; i <= maxTID; ++i) {
				s.executeQuery("select MAX(DATE_STAMP_) from TRENDDATA " +
						"where TID_ = " + i + ";");
				String date = null;

				if(rs.next()) {
					date = rs.getString(1);
				} else 
					throw new SQLException("Unable to find max date stamp for TID " + i);

				rs.close();
				s.close();

				Statement ws = webctrl.createStatement();
				ws.executeQuery ("select * from TRENDDATA " +
						"where DATE_STAMP_ > \"" + date + "\" AND TID_ = " + i + ";");
				ResultSet wrs = ws.getResultSet();

				PreparedStatement ps = longterm.prepareStatement("INSERT INTO TRENDDATA VALUES (?, ?, ?, ?, ?, ?)");

				// determine properties of table
				ResultSetMetaData meta = wrs.getMetaData();

				// transfer data from result set
				while(wrs.next()) {
					for(int col = 0; col < meta.getColumnCount(); col++) {
						ps.setObject(col + 1, wrs.getObject(col + 1));
					}

					int rowct = ps.executeUpdate();
					if(rowct != 1) {
						log.warn("Database Updater expected return value of 1 for insert statement.");
					}
				}
				
				wrs.close();
				ps.close();
				ws.close();
			}
		} catch (SQLException e) {
			log.error("Database Updater encountered an error: " + e.getMessage());
			ErrorDatabaseResult.activateError(ErrorDatabaseResult.LONG_TERM_FAIL);
			ErrorDatabaseResult.activateError(ErrorDatabaseResult.WEBCTRL_FAIL);
		}

		WebCTRLDatabaseConnector.closeConnection(webctrl);
		LongTermDatabaseConnector.closeConnection(longterm);
	}
}
